{
"metadata": {
"name": "",
"signature": "sha256:e9a51db68a345ff7726ae5b55a45c5d5fc5b6fd478b5fb7cfdc0df20b9592dbe"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "code",
"collapsed": false,
"input": [
"import pandas as pd\n",
"import sqlite3"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 9.1 Reading data from SQL databases"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"So far we've only talked about reading data from CSV files. That's a pretty common way to store data, but there are many others! Pandas can read from HTML, JSON, SQL, Excel (!!!), HDF5, Stata, and a few other things. In this chapter we'll talk about reading data from SQL databases.\n",
"\n",
"You can read data from a SQL database using the `pd.read_sql` function. `read_sql` will automatically convert SQL column names to DataFrame column names.\n",
"\n",
"`read_sql` takes 2 arguments: a `SELECT` statement, and a database connection object. This is great because it means you can read from *any* kind of SQL database -- it doesn't matter if it's MySQL, SQLite, PostgreSQL, or something else.\n",
"\n",
"This example reads from a SQLite database, but any other database would work the same way."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# Let's look at the database file\n",
"! head ../data/weather_2012.sqlite"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"SQLite format 3\u0000\u0004\u0000\u0001\u0001\u0000@ \u0000\u0000\u0000\u0004\u0000\u0000\u0000\u0007\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0003\u0000\u0000\u0000\u0004\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0001\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0004\u0000-\ufffd%\r",
"\u0000\u0000\u0000\u0002\u0002\ufffd\u0000\u0003>\u0002\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000P\u0002\u0006\u0017++\u0001Ytablesqlite_sequencesqlite_sequence\u0003CREATE TABLE sqlite_sequence(name,seq)\ufffd?\u0001\u0007\u0017%%\u0001\ufffdAtableweather_2012weather_2012\u0002CREATE TABLE weather_2012 (\r\n",
" id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n",
" date_time TIMESTAMP,\r\n",
" temp DOUBLE PRECISION\r\n",
" "
]
},
{
"output_type": "stream",
"stream": "stdout",
"text": [
" )\u0005\u0000\u0000\u0000\u0003\u0003\ufffd\u0000\u0000\u0000\u0000\u0007\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001z\u0001Y\u0001?\u0001\u001e",
"\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000y\u0000X\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u001d",
"\u0004\u00003\u00072012-01-02 04:00:00@\u0007333333\u001f\u001c",
"\u0004\u00003\u00072012-01-02 03:00:00@\r",
"\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u001b\u0004\u00003\u00072012-01-02 02:00:00@\u000f333333\u001f\u001a\u0004\u00003\u00072012-01-02 01:00:00@\u0012ffffff\u001f\u0019\u0004\u00003\u00072012-01-02 00:00:00@\u0014\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0018\u0004\u00003\u00072012-01-01 23:00:00@\u0015333333\u001f\u0017\u0004\u00003\u00072012-01-01 22:00:00@\u0011\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018\u0016\u0004\u00003\u00012012-01-01 21:00:00\u0004\u001f\u0015\u0004\u00003\u00072012-01-01 20:00:00@\t\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0014\u0004\u00003\u00072012-01-01 19:00:00@\b\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0013\u0004\u00003\u00072012-01-01 18:00:00@\u000effffff\u0018\u0012\u0004\u00003\u00012012-01-01 17:00:00\u0003\u001f\u0011\u0004\u00003\u00072012-01-01 16:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0010\u0004\u00003\u00072012-01-01 15:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u000f\u0004\u00003\u00072012-01-01 14:00:00?\u9659\ufffd\ufffd\ufffd\ufffd\u001f\u000e\u0004\u00003\u00072012-01-01 13:00:00?\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\r",
"\u0004\u00003\u00072012-01-01 12:00:00\ufffd\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\f",
"\u0004\u00003\u00072012-01-01 11:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0018\u000b",
"\u0004\u00003\u00012012-01-01 10:00:00\ufffd\u001f\r\n",
"\u0004\u00003\u00072012-01-01 09:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\t\u0004\u00003\u00072012-01-01 08:00:00\ufffd\ufffdffffff\u001f\b\u0004\u00003\u00072012-01-01 07:00:00\ufffd\ufffdffffff\u001f\u0007\u0004\u00003\u00072012-01-01 06:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0006\u0004\u00003\u00072012-01-01 05:00:00\ufffd\ufffdffffff\u001f\u0005\u0004\u00003\u00072012-01-01 04:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0004\u0004\u00003\u00072012-01-01 03:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0003\u0004\u00003\u00072012-01-01 02:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0002\u0004\u00003\u00072012-01-01 01:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0001\u0004\u00003\u00072012-01-01 0\u0000\u0000\u0000\u0006X\u0000\u0000\u0000\u0005;\u0000\u0000\u0000\u0004\u001d",
"\r",
"\u0000\u0000\u0000\u0001\u0003\ufffd\u0000\u0003\ufffd\u0000\u0002\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0010\u0001\u0003%\u0001weather_2012d\r",
"\u0000\u0000\u0000\u001d",
"\u0000X\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001z\u0001Y\u0001?\u0001\u001e",
"\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000y\u0000X\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u001d",
"\u0004\u00003\u00072012-01-02 04:00:00@\u0007333333\u001f\u001c",
"\u0004\u00003\u00072012-01-02 03:00:00@\r",
"\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u001b\u0004\u00003\u00072012-01-02 02:00:00@\u000f333333\u001f\u001a\u0004\u00003\u00072012-01-02 01:00:00@\u0012ffffff\u001f\u0019\u0004\u00003\u00072012-01-02 00:00:00@\u0014\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0018\u0004\u00003\u00072012-01-01 23:00:00@\u0015333333\u001f\u0017\u0004\u00003\u00072012-01-01 22:00:00@\u0011\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018\u0016\u0004\u00003\u00012012-01-01 21:00:00\u0004\u001f\u0015\u0004\u00003\u00072012-01-01 20:00:00@\t\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0014\u0004\u00003\u00072012-01-01 19:00:00@\b\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0013\u0004\u00003\u00072012-01-01 18:00:00@\u000effffff\u0018\u0012\u0004\u00003\u00012012-01-01 17:00:00\u0003\u001f\u0011\u0004\u00003\u00072012-01-01 16:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0010\u0004\u00003\u00072012-01-01 15:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u000f\u0004\u00003\u00072012-01-01 14:00:00?\u9659\ufffd\ufffd\ufffd\ufffd\u001f\u000e\u0004\u00003\u00072012-01-01 13:00:00?\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\r",
"\u0004\u00003\u00072012-01-01 12:00:00\ufffd\u0259\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\f",
"\u0004\u00003\u00072012-01-01 11:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u0018\u000b",
"\u0004\u00003\u00012012-01-01 10:00:00\ufffd\u001f\r\n",
"\u0004\u00003\u00072012-01-01 09:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\t\u0004\u00003\u00072012-01-01 08:00:00\ufffd\ufffdffffff\u001f\b\u0004\u00003\u00072012-01-01 07:00:00\ufffd\ufffdffffff\u001f\u0007\u0004\u00003\u00072012-01-01 06:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0006\u0004\u00003\u00072012-01-01 05:00:00\ufffd\ufffdffffff\u001f\u0005\u0004\u00003\u00072012-01-01 04:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0004\u0004\u00003\u00072012-01-01 03:00:00\ufffd\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\u0003\u0004\u00003\u00072012-01-01 02:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0002\u0004\u00003\u00072012-01-01 01:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f\u0001\u0004\u00003\u00072012-01-01 00:00:00\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
"\u0000\u0000\u0000\u001e",
"\u0000M\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003b\u0003A\u0003 \u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002b\u0002A\u0002 \u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001h\u0001G\u0001&\u0001\u0005\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000n\u0000M\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001f;\u0004\u00003\u00072012-01-03 10:00:00\ufffd.\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018:\u0004\u00003\u00012012-01-03 09:00:00\ufffd\u001f9\u0004\u00003\u00072012-01-03 08:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u00188\u0004\u00003\u00012012-01-03 07:00:00\ufffd\u001f7\u0004\u00003\u00072012-01-03 06:00:00\ufffd*\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f6\u0004\u00003\u00072012-01-03 05:00:00\ufffd)\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f5\u0004\u00003\u00072012-01-03 04:00:00\ufffd)333333\u001f4\u0004\u00003\u00072012-01-03 03:00:00\ufffd&\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f3\u0004\u00003\u00072012-01-03 02:00:00\ufffd%\u0000\u0000\u0000\u0000\u0000\u0000\u001f2\u0004\u00003\u00072012-01-03 01:00:00\ufffd#ffffff\u00181\u0004\u00003\u00012012-01-03 00:00:00\ufffd\u001f0\u0004\u00003\u00072012-01-02 23:00:00\ufffd\u001d",
"\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u0018/\u0004\u00003\u00012012-01-02 22:00:00\ufffd\u001f.\u0004\u00003\u00072012-01-02 21:00:00\ufffd\u0017333333\u001f-\u0004\u00003\u00072012-01-02 20:00:00\ufffd\u0016ffffff\u001f,\u0004\u00003\u00072012-01-02 19:00:00\ufffd\u0013333333\u001f+\u0004\u00003\u00072012-01-02 18:00:00\ufffd\u0010ffffff\u001f*\u0004\u00003\u00072012-01-02 17:00:00\ufffd\u0000\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f)\u0004\u00003\u00072012-01-02 16:00:00\ufffd\ufffdffffff\u0017(\u0004\u00003\b2012-01-02 15:00:00\u001f'\u0004\u00003\u00072012-01-02 14:00:00?\ud925\ude59\ufffd\ufffd\ufffd\u001f&\u0004\u00003\u00072012-01-02 13:00:00?\ud925\ude59\ufffd\ufffd\ufffd\u001f%\u0004\u00003\u00072012-01-02 12:00:00?\ufffd333333\u001f$\u0004\u00003\u00072012-01-02 11:00:00@\u0001\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f#\u0004\u00003\u00072012-01-02 10:00:00?\ufffd\u0000\u0000\u0000\u0000\u0000\u0000\u001f\"\u0004\u00003\u00072012-01-02 09:00:00?\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f!\u0004\u00003\u00072012-01-02 08:00:00?\ufffdffffff\u0018 \u0004\u00003\u00012012-01-02 07:00:00\u0002\u001f\u001f\u0004\u00003\u00072012-01-02 06:00:00@\u0002ffffff\u001f\u001e",
"\u0004\u00003\u00072012-01-02 05:00:00@\u0004\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
"\u0000\u0000\u0000\u001d",
"\u0000J\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0002Z\u00029\u0002\u0018\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001\ufffd\u0001s\u0001R\u00011\u0001\u0010\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000\ufffd\u0000k\u0000J\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u001fX\u0004\u00003\u00072012-01-04 15:00:00\ufffd$ffffff\u001fW\u0004\u00003\u00072012-01-04 14:00:00\ufffd&\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fV\u0004\u00003\u00072012-01-04 13:00:00\ufffd(\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fU\u0004\u00003\u00072012-01-04 12:00:00\ufffd+ffffff\u001fT\u0004\u00003\u00072012-01-04 11:00:00\ufffd,ffffff\u001fS\u0004\u00003\u00072012-01-04 10:00:00\ufffd.ffffff\u001fR\u0004\u00003\u00072012-01-04 09:00:00\ufffd0\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fQ\u0004\u00003\u00072012-01-04 08:00:00\ufffd1\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fP\u0004\u00003\u00072012-01-04 07:00:00\ufffd2333333\u001fO\u0004\u00003\u00072012-01-04 06:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fN\u0004\u00003\u00072012-01-04 05:00:00\ufffd2\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fM\u0004\u00003\u00072012-01-04 04:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fL\u0004\u00003\u00072012-01-04 03:00:00\ufffd2\ufffd\u0000\u0000\u0000\u0000\u0000\u001fK\u0004\u00003\u00072012-01-04 02:00:00\ufffd2\u0019\ufffd\ufffd\ufffd\ufffd\ufffd\u001fJ\u0004\u00003\u00072012-01-04 01:00:00\ufffd1\ufffdfffff\u001fI\u0004\u00003\u00072012-01-04 00:00:00\ufffd1\ufffd\u0000\u0000\u0000\u0000\u0000\u001fH\u0004\u00003\u00072012-01-03 23:00:00\ufffd1L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fG\u0004\u00003\u00072012-01-03 22:00:00\ufffd1\u0019\ufffd\ufffd\ufffd\ufffd\ufffd\u0018F\u0004\u00003\u00012012-01-03 21:00:00\ufffd\u001fE\u0004\u00003\u00072012-01-03 20:00:00\ufffd1L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fD\u0004\u00003\u00072012-01-03 19:00:00\ufffd0\ufffdfffff\u001fC\u0004\u00003\u00072012-01-03 18:00:00\ufffd0L\ufffd\ufffd\ufffd\ufffd\ufffd\u001fB\u0004\u00003\u00072012-01-03 17:00:00\ufffd/\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fA\u0004\u00003\u00072012-01-03 16:00:00\ufffd.\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f@\u0004\u00003\u00072012-01-03 15:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f?\u0004\u00003\u00072012-01-03 14:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f>\u0004\u00003\u00072012-01-03 13:00:00\ufffd.333333\u001f=\u0004\u00003\u00072012-01-03 12:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f<\u0004\u00003\u00072012-01-03 11:00:00\ufffd-\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\r",
"\u0000\u0000\u0000\f",
"\u0002{\u0000\u0003\ufffd\u0003\ufffd\u0003\ufffd\u0003|\u0003[\u0003:\u0003\u0019\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002\ufffd\u0002{\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0018d\u0004\u00003\u00012012-01-05 03:00:00\ufffd\u001fc\u0004\u00003\u00072012-01-05 02:00:00\ufffd\u0015\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fb\u0004\u00003\u00072012-01-05 01:00:00\ufffd\u001e",
"\u0000\u0000\u0000\u0000\u0000\u0000\u001fa\u0004\u00003\u00072012-01-05 00:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f`\u0004\u00003\u00072012-01-04 23:00:00\ufffd#333333\u001f_\u0004\u00003\u00072012-01-04 22:00:00\ufffd#\u0000\u0000\u0000\u0000\u0000\u0000\u001f^\u0004\u00003\u00072012-01-04 21:00:00\ufffd\u001e",
"ffffff\u001f]\u0004\u00003\u00072012-01-04 20:00:00\ufffd\u001f333333\u001f\\\u0004\u00003\u00072012-01-04 19:00:00\ufffd \ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001f[\u0004\u00003\u00072012-01-04 18:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fZ\u0004\u00003\u00072012-01-04 17:00:00\ufffd!\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd\u001fY\u0004\u00003\u00072012-01-04 16:00:00\ufffd\"\ufffd\ufffd\ufffd\ufffd\ufffd\ufffd"
]
}
],
"prompt_number": 3
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"con = sqlite3.connect(\"../data/weather_2012.sqlite\")\n",
"df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con)\n",
"df"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" id | \n",
" date_time | \n",
" temp | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" 2012-01-01 00:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" 2012-01-01 01:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" 2012-01-01 02:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 22,
"text": [
" id date_time temp\n",
"0 1 2012-01-01 00:00:00 -1.8\n",
"1 2 2012-01-01 01:00:00 -1.8\n",
"2 3 2012-01-01 02:00:00 -1.8"
]
}
],
"prompt_number": 22
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`read_sql` doesn't automatically set the primary key (`id`) to be the index of the dataframe. You can make it do that by adding an `index_col` argument to `read_sql`. \n",
"\n",
"If you've used `read_csv` a lot, you may have seen that it has an `index_col` argument as well. This one behaves the same way."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con, index_col='id')\n",
"df"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" date_time | \n",
" temp | \n",
"
\n",
" \n",
" id | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 1 | \n",
" 2012-01-01 00:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 2 | \n",
" 2012-01-01 01:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 3 | \n",
" 2012-01-01 02:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 23,
"text": [
" date_time temp\n",
"id \n",
"1 2012-01-01 00:00:00 -1.8\n",
"2 2012-01-01 01:00:00 -1.8\n",
"3 2012-01-01 02:00:00 -1.8"
]
}
],
"prompt_number": 23
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you want your dataframe to be indexed by more than one column, you can give a list of columns to `index_col`:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con, \n",
" index_col=['id', 'date_time'])\n",
"df"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" | \n",
" temp | \n",
"
\n",
" \n",
" id | \n",
" date_time | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 1 | \n",
" 2012-01-01 00:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 2 | \n",
" 2012-01-01 01:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
" 3 | \n",
" 2012-01-01 02:00:00 | \n",
" -1.8 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 24,
"text": [
" temp\n",
"id date_time \n",
"1 2012-01-01 00:00:00 -1.8\n",
"2 2012-01-01 01:00:00 -1.8\n",
"3 2012-01-01 02:00:00 -1.8"
]
}
],
"prompt_number": 24
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Your turn\n",
"* How many rows are there in this database?\n",
"* What is the average temperature?\n",
"* What are the first and last dates?"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"df = pd.read_sql(\"SELECT * from weather_2012 \", con, \n",
" index_col=['id', 'date_time'])\n",
"print df.describe()\n",
"df.sort().head(), df.sort().tail()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
" temp\n",
"count 100.000000\n",
"mean -6.841000\n",
"std 7.774487\n",
"min -18.600000\n",
"25% -14.825000\n",
"50% -7.450000\n",
"75% 0.350000\n",
"max 5.300000\n"
]
},
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 8,
"text": [
"( temp\n",
" id date_time \n",
" 1 2012-01-01 00:00:00 -1.8\n",
" 2 2012-01-01 01:00:00 -1.8\n",
" 3 2012-01-01 02:00:00 -1.8\n",
" 4 2012-01-01 03:00:00 -1.5\n",
" 5 2012-01-01 04:00:00 -1.5, temp\n",
" id date_time \n",
" 96 2012-01-04 23:00:00 -9.6\n",
" 97 2012-01-05 00:00:00 -8.8\n",
" 98 2012-01-05 01:00:00 -7.5\n",
" 99 2012-01-05 02:00:00 -5.4\n",
" 100 2012-01-05 03:00:00 -5.0)"
]
}
],
"prompt_number": 8
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 9.2 Writing to a SQLite database"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas has a `write_frame` function which creates a database table from a dataframe. Right now this only works for SQLite databases. Let's use it to move our 2012 weather data into SQL.\n",
"\n",
"You'll notice that this function is in `pd.io.sql`. There are a ton of useful functions for reading and writing various kind of data in `pd.io`, and it's worth spending some time exploring them. ([see the documentation!](http://pandas.pydata.org/pandas-docs/stable/io.html))"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"weather_df = pd.read_csv('../data/weather_2012.csv')\n",
"con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
"con.execute(\"DROP TABLE IF EXISTS weather_2012\")\n",
"pd.io.sql.write_frame(weather_df, \"weather_2012\", con)"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stderr",
"text": [
"/Users/admin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/sql.py:1607: FutureWarning: write_frame is deprecated, use to_sql\n",
" warnings.warn(\"write_frame is deprecated, use to_sql\", FutureWarning)\n",
"/Users/admin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/io/sql.py:1309: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
" warnings.warn(_SAFE_NAMES_WARNING)\n"
]
}
],
"prompt_number": 9
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"## OK we follow the advice of the warning and use the new function.\n",
"con.execute(\"DROP TABLE IF EXISTS weather_2012\") # don't forget to rop the table first! \n",
"pd.io.sql.to_sql(weather_df, \"weather_2012\", con)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 15
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can now read from the `weather_2012` table in `test_db.sqlite`, and we see that we get the same data back:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
"df = pd.read_sql(\"SELECT * from weather_2012 LIMIT 3\", con)\n",
"df"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" index | \n",
" Date/Time | \n",
" Temp (C) | \n",
" Dew Point Temp (C) | \n",
" Rel Hum (%) | \n",
" Wind Spd (km/h) | \n",
" Visibility (km) | \n",
" Stn Press (kPa) | \n",
" Weather | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 0 | \n",
" 2012-01-01 00:00:00 | \n",
" -1.8 | \n",
" -3.9 | \n",
" 86 | \n",
" 4 | \n",
" 8 | \n",
" 101.24 | \n",
" Fog | \n",
"
\n",
" \n",
" 1 | \n",
" 1 | \n",
" 2012-01-01 01:00:00 | \n",
" -1.8 | \n",
" -3.7 | \n",
" 87 | \n",
" 4 | \n",
" 8 | \n",
" 101.24 | \n",
" Fog | \n",
"
\n",
" \n",
" 2 | \n",
" 2 | \n",
" 2012-01-01 02:00:00 | \n",
" -1.8 | \n",
" -3.4 | \n",
" 89 | \n",
" 7 | \n",
" 4 | \n",
" 101.26 | \n",
" Freezing Drizzle,Fog | \n",
"
\n",
" \n",
"
\n",
"
"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 16,
"text": [
" index Date/Time Temp (C) Dew Point Temp (C) Rel Hum (%) \\\n",
"0 0 2012-01-01 00:00:00 -1.8 -3.9 86 \n",
"1 1 2012-01-01 01:00:00 -1.8 -3.7 87 \n",
"2 2 2012-01-01 02:00:00 -1.8 -3.4 89 \n",
"\n",
" Wind Spd (km/h) Visibility (km) Stn Press (kPa) Weather \n",
"0 4 8 101.24 Fog \n",
"1 4 8 101.24 Fog \n",
"2 7 4 101.26 Freezing Drizzle,Fog "
]
}
],
"prompt_number": 16
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The nice thing about having your data in a database is that you can do arbitrary SQL queries. This is cool especially if you're more familiar with SQL. Here's an example of sorting by the Weather column:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"con = sqlite3.connect(\"../data/test_db.sqlite\")\n",
"df = pd.read_sql(\"SELECT * from weather_2012 ORDER BY Weather LIMIT 3\", con)\n",
"df"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" index | \n",
" Date/Time | \n",
" Temp (C) | \n",
" Dew Point Temp (C) | \n",
" Rel Hum (%) | \n",
" Wind Spd (km/h) | \n",
" Visibility (km) | \n",
" Stn Press (kPa) | \n",
" Weather | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 67 | \n",
" 2012-01-03 19:00:00 | \n",
" -16.9 | \n",
" -24.8 | \n",
" 50 | \n",
" 24 | \n",
" 25 | \n",
" 101.74 | \n",
" Clear | \n",
"
\n",
" \n",
" 1 | \n",
" 114 | \n",
" 2012-01-05 18:00:00 | \n",
" -7.1 | \n",
" -14.4 | \n",
" 56 | \n",
" 11 | \n",
" 25 | \n",
" 100.71 | \n",
" Clear | \n",
"
\n",
" \n",
" 2 | \n",
" 115 | \n",
" 2012-01-05 19:00:00 | \n",
" -9.2 | \n",
" -15.4 | \n",
" 61 | \n",
" 7 | \n",
" 25 | \n",
" 100.80 | \n",
" Clear | \n",
"
\n",
" \n",
"
\n",
"
"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 26,
"text": [
" index Date/Time Temp (C) Dew Point Temp (C) Rel Hum (%) \\\n",
"0 67 2012-01-03 19:00:00 -16.9 -24.8 50 \n",
"1 114 2012-01-05 18:00:00 -7.1 -14.4 56 \n",
"2 115 2012-01-05 19:00:00 -9.2 -15.4 61 \n",
"\n",
" Wind Spd (km/h) Visibility (km) Stn Press (kPa) Weather \n",
"0 24 25 101.74 Clear \n",
"1 11 25 100.71 Clear \n",
"2 7 25 100.80 Clear "
]
}
],
"prompt_number": 26
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Your turn\n",
"* Use SQL to only put the columns temp and wind_spd in the dataframe and make the date/time the index"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"#your code here"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 35
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you have a PostgreSQL database or MySQL database, reading from it works exactly the same way as reading from a SQLite database. You create a connection using `psycopg2.connect()` or `MySQLdb.connect()`, and then use\n",
"\n",
"`pd.read_sql(\"SELECT whatever from your_table\", con)`"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 9.3 Connecting to other kinds of database"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To connect to a MySQL database:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import MySQLdb\n",
"con = MySQLdb.connect(host=\"localhost\", db=\"test\")"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To connect to a PostgreSQL database:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import psycopg2\n",
"con = psycopg2.connect(host=\"localhost\")"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"